{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用addressparser工具"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0  客户ID  客户编码         客户名称   省份   城市  地址\n",
      "0           1     1   123      富士康科技集团  广东省  深圳市 NaN\n",
      "1           2     2    45   华德控股集团有限公司  浙江省  宁波市 NaN\n",
      "2           3     3    78  凯利达科技股份有限公司  广东省  深圳市 NaN\n",
      "3           4     4   910   华荣科技股份有限公司  广东省  深圳市 NaN\n",
      "4           5     5    11   中建科技集团有限公司  广东省  深圳市 NaN\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import addressparser\n",
    "\n",
    "data = pd.read_excel(r'地址.xlsx')\n",
    "print(data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     省    市 区 地名\n",
      "0  广东省  深圳市     \n",
      "1  浙江省  宁波市     \n",
      "2  广东省  深圳市     \n",
      "3  广东省  深圳市     \n",
      "4  广东省  深圳市     \n"
     ]
    }
   ],
   "source": [
    "df = addressparser.transform(data[\"城市\"])\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"省份\"] = df[\"省\"]\n",
    "#data[\"城市\"] = df[\"市\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0  客户ID  客户编码         客户名称   省份   城市  地址\n",
      "0           1     1   123      富士康科技集团  广东省  深圳市 NaN\n",
      "1           2     2    45   华德控股集团有限公司  浙江省  宁波市 NaN\n",
      "2           3     3    78  凯利达科技股份有限公司  广东省  深圳市 NaN\n",
      "3           4     4   910   华荣科技股份有限公司  广东省  深圳市 NaN\n",
      "4           5     5    11   中建科技集团有限公司  广东省  深圳市 NaN\n"
     ]
    }
   ],
   "source": [
    "print(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_excel(\"地址.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 高德API（高德开放平台）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "嘉兴市\n"
     ]
    }
   ],
   "source": [
    "payload = {'key': '改为自己申请的地址', 'keywords': data[\"客户名称\"][1]}\n",
    "r = requests.get('https://restapi.amap.com/v3/place/text?parameters',params=payload)\n",
    "txt = json.loads(r.text)\n",
    "city = txt[\"suggestion\"][\"cities\"][0][\"name\"]\n",
    "print(city)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for id in range(len(data)):\n",
    "    payload = {'key': '改为自己申请的地址', 'keywords': data[\"客户名称\"][id]}\n",
    "    r = requests.get('https://restapi.amap.com/v3/place/text?parameters',params=payload)\n",
    "    txt = json.loads(r.text)\n",
    "    try:\n",
    "        #province = txt[\"suggestion\"][0][\"pname\"]\n",
    "        city = txt[\"suggestion\"][\"cities\"][0][\"name\"]\n",
    "        #data[\"省份\"][id] = province\n",
    "        data[\"城市\"][id] = city \n",
    "    except:\n",
    "        print(txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>客户ID</th>\n",
       "      <th>客户编码</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>地址</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>123</td>\n",
       "      <td>富士康科技集团</td>\n",
       "      <td>广东省</td>\n",
       "      <td>深圳市</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>45</td>\n",
       "      <td>华德控股集团有限公司</td>\n",
       "      <td>浙江省</td>\n",
       "      <td>宁波市</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>78</td>\n",
       "      <td>凯利达科技股份有限公司</td>\n",
       "      <td>广东省</td>\n",
       "      <td>汕头市</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>910</td>\n",
       "      <td>华荣科技股份有限公司</td>\n",
       "      <td>广东省</td>\n",
       "      <td>深圳市</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>中建科技集团有限公司</td>\n",
       "      <td>广东省</td>\n",
       "      <td>深圳市</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  客户ID  客户编码         客户名称   省份   城市  地址\n",
       "0           1     1   123      富士康科技集团  广东省  深圳市 NaN\n",
       "1           2     2    45   华德控股集团有限公司  浙江省  宁波市 NaN\n",
       "2           3     3    78  凯利达科技股份有限公司  广东省  汕头市 NaN\n",
       "3           4     4   910   华荣科技股份有限公司  广东省  深圳市 NaN\n",
       "4           5     5    11   中建科技集团有限公司  广东省  深圳市 NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_excel(\"地址.xlsx\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
